import java.sql.*;
import java.util.*;
/**
 * Created by zyl on 2017/6/8.
 */
public class MessageDAO {
    private String url;
    private String user;
    private String passwd;

    public MessageDAO(String url, String user, String passwd) {
        this.url = url;
        this.user = user;
        this.passwd = passwd;
    }

    public void add(Message message) {
        try(Connection conn = DriverManager.getConnection(url, user, passwd);
            Statement statement = conn.createStatement()) {
            String sql = String.format(
                    "INSERT INTO city( Name, Population) VALUES ('%s', '%d')",
                    message.getName(),message.getPopulation());
            statement.executeUpdate(sql);
        } catch(SQLException ex) {
            throw new RuntimeException(ex);
        }
    }

    public List<Message> get() {
        List<Message> messages = new ArrayList<>();
        try(Connection conn = DriverManager.getConnection(url, user, passwd);
            Statement statement = conn.createStatement()) {
            ResultSet result =
                    statement.executeQuery(
                            "SELECT DISTINCT city.Name,city.Population\n" +
                                    "FROM  city INNER JOIN\n" +
                                    "        country ON country.Capital = city.ID\n" +
                                    "WHERE  country.Continent = \"Asia\"");
            while (result.next()) {
                Message message = toCity(result);
                messages.add(message);
            }
        } catch(SQLException ex) {
            throw new RuntimeException(ex);
        }
        return messages;
    }

    private Message toCity(ResultSet result) throws SQLException {
        Message message = new Message();
        message.setName(result.getString(1));
        message.setPopulation(result.getLong(2));
        return message;
    }
}